package zz.extjs.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.json.JSONObject;

import zz.extjs.dao.StudentDAO;
import zz.extjs.dbc.DataBaseConnection;
import zz.extjs.vo.Student;

public class StudentDAOImpl implements StudentDAO {

	// 处理日期，用于t_pbbegin与t_pbout相加计算出pb_timeout
	// 参数d：t_pbbegin日期；参数off：t_pbout偏移量
	private Date getDateAdd(Date d, String off) {
		Date result = null;
		if (d != null && off != null) {
			long afterTime = (d.getTime() / 1000) + 60 * 60 * 24
					* Integer.parseInt(off);
			d.setTime(afterTime * 1000);
			result = d;
		}
		return result;
	}

	// 直接运行sql语句，不返回记录，用于delete、insert、update等
	// 返回，正常为0 ，异常为SQLException错误码,flag为select执行查询，返回记录数量
	public int ExcuteSQL(String sql, String flag) throws Exception {
		int r = 0;
		PreparedStatement pstmt = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			if ("select".equals(flag)) {
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) // 有记录返回
					r = 1;
				rs.close();
			} else
				r = pstmt.executeUpdate();
			pstmt.close();
			// r = 1;
		} catch (SQLException e) {
			r = e.getErrorCode();
		} finally {
			dbc.close();
		}
		return r;
	}

	public Student getStudent(String sid) throws Exception {

		Student stu = null;
		sid = sid.trim();
		String sql = "SELECT * FROM PERSON_INFO_TAB p,COURSE_TAB,EXAM_LIST_TAB,SUBJECT_TAB ";
		sql += " WHERE p.COS_ID=COURSE_TAB.COS_ID AND p.PER_ID=EXAM_LIST_TAB.PER_ID AND EXAM_LIST_TAB.SUB_ID=SUBJECT_TAB.SUB_ID ";
		sql += " AND p.T_SID = '" + sid + "'";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			// pstmt.setString(1, sid);
			// pstmt.setString(2, member.getPassword());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 有符合查询条件的记录
				stu = new Student();
				stu.setId(rs.getString("per_id"));
				stu.setBirthday(rs.getDate("T_BIRTHDAY"));
				stu.setSid(rs.getString("T_SID"));
				stu.setEname(rs.getString("E_NAME"));
				stu.setCname(rs.getString("C_NAME"));
				stu.setKname(rs.getString("K_NAME"));
				stu.setPass_id(rs.getString("PASS_ID"));
				stu.setSex(rs.getString("T_SEX"));
				stu.setIclass(rs.getString("T_CLASS"));
				stu.setStudy(rs.getString("T_STUDY"));
				// stu.setPw(rs.getString("P_W")); //只有pwmng页面请求才发送
				stu.setHasstucard(rs.getInt("HAS_STUCARD"));
				stu.setHasphoto(rs.getInt("HAS_PHOTO"));

				stu.setPb_id(rs.getString("PB_ID"));
				stu.setMemo(rs.getString("MEMO"));
				stu.setPb_type(rs.getString("PB_TYPE"));
				stu.setPb_out(rs.getString("T_PBOUT"));
				stu.setPb_begin(rs.getDate("T_PBBEGIN"));
				stu.setPbtimeout(getDateAdd(rs.getDate("T_PBBEGIN"), rs.getString("T_PBOUT")));

				do {
					stu.addResult(rs.getString("sub_name"), rs
							.getString("scope_tab"));
				} while (rs.next());

			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("dao-getstudent error");
		} finally {
			dbc.close();
		}
		return stu;
	}

	public Iterator<JSONObject> getList(String sql) throws Exception {
		// 返回sql查询的student，sql中必须包含subname、examid、scopetab等，否则出错

		// Student stu = null;
		List<JSONObject> result = new ArrayList<JSONObject>();

		// String sql =
		// "SELECT * FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
		// sql +=
		// " left join EXAM_LIST_TAB e on p.PER_ID=e.PER_ID left join SUBJECT_TAB s on e.SUB_ID=s.SUB_ID ";
		// sql += sqlwhere;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			// pstmt.setString(1, sid);
			// pstmt.setString(2, member.getPassword());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 有符合查询条件的记录
				int i = -1;
				do {
					/*
					 * if(result.size()<=0 ||
					 * !(result.get(i).getSid().equals(rs.getString("T_SID")))){
					 */
					Student stu = new Student();
					/**
					 * 发送非成绩页面的数据 if (rs.findColumn("EXAM_ID")>=0){
					 * stu.setId(rs.getString("EXAM_ID")); // 有成绩，则添加相应项目
					 * stu.addResult(rs.getString("sub_name"),
					 * rs.getString("scope_tab")); }
					 */
					stu.setId(rs.getString("per_id"));
					stu.setBirthday(rs.getDate("T_BIRTHDAY"));
					stu.setSid(rs.getString("T_SID"));
					stu.setEname(rs.getString("E_NAME"));
					stu.setCname(rs.getString("C_NAME"));
					stu.setKname(rs.getString("K_NAME"));
					stu.setPb_id(rs.getString("PB_ID"));
					stu.setSex(rs.getString("T_SEX"));
					stu.setIclass(rs.getString("T_CLASS"));
					stu.setStudy(rs.getString("T_STUDY"));
					stu.setCourse(rs.getString("COS_NAME"));
					stu.setPw(rs.getString("P_W"));
					stu.setHasstucard(rs.getInt("HAS_STUCARD"));

					stu.setMemo(rs.getString("MEMO")); // 图书证号
					stu.setPb_type(rs.getString("PB_TYPE")); // 签证号
					stu.setPb_out(rs.getString("T_PBOUT"));
					stu.setPb_begin(rs.getDate("T_PBBEGIN"));
					stu.setPbtimeout(getDateAdd(rs.getDate("T_PBBEGIN"), rs.getString("T_PBOUT")));
					
					JSONObject jObject = new JSONObject(stu);
					result.add(jObject);
					i++;

					// if (rs.getString("sub_name")!=null)
				} while (rs.next());

			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("dao-getlist error");
		} finally {
			dbc.close();
		}

		return result.iterator();

	}

	public Iterator<JSONObject> getExamList(String sql) throws Exception {

		// Student stu = null;
		List<JSONObject> result = new ArrayList<JSONObject>();

		// String sql =
		// "SELECT * FROM PERSON_INFO_TAB p left join COURSE_TAB c on p.COS_ID=c.COS_ID ";
		// sql +=
		// " left join EXAM_LIST_TAB e on p.PER_ID=e.PER_ID left join SUBJECT_TAB s on e.SUB_ID=s.SUB_ID ";
		// sql += sqlwhere;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			pstmt = dbc.getConnection().prepareStatement(sql);
			// pstmt.setString(1, sid);
			// pstmt.setString(2, member.getPassword());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 有符合查询条件的记录
				int i = -1;
				do {
					Student stu = new Student();
					// 发送成绩页面的数据
					if (rs.getString("EXAM_ID") != null) {
						stu.setId(rs.getString("EXAM_ID")); // 有成绩，则添加相应项目
						stu.addResult(rs.getString("sub_name"), rs
								.getString("scope_tab"));
					}

					stu.setBirthday(rs.getDate("T_BIRTHDAY"));
					stu.setSid(rs.getString("T_SID"));
					stu.setEname(rs.getString("E_NAME"));
					stu.setCname(rs.getString("C_NAME"));
					stu.setKname(rs.getString("K_NAME"));
					stu.setPb_id(rs.getString("PB_ID"));
					stu.setSex(rs.getString("T_SEX"));
					stu.setIclass(rs.getString("T_CLASS"));
					stu.setStudy(rs.getString("T_STUDY"));
					stu.setCourse(rs.getString("COS_NAME"));
					stu.setPw(rs.getString("P_W"));
					stu.setHasstucard(rs.getInt("HAS_STUCARD"));

					stu.setPb_id(rs.getString("PB_ID"));
					stu.setMemo(rs.getString("MEMO"));
					stu.setPb_type(rs.getString("PB_TYPE"));
					stu.setPb_out(rs.getString("T_PBOUT"));
					stu.setPb_begin(rs.getDate("T_PBBEGIN"));
					stu.setPbtimeout(getDateAdd(rs.getDate("T_PBBEGIN"), rs.getString("T_PBOUT")));

					JSONObject jObject = new JSONObject(stu);
					result.add(jObject);
					i++;

					// if (rs.getString("sub_name")!=null)
				} while (rs.next());

			}
			rs.close();
			pstmt.close();
		} catch (Exception e) {
			throw new Exception("dao-getexamlist error");
		} finally {
			dbc.close();
		}
		return result.iterator();
	}
}